"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : 导出导入alter table相关语法
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库下建表后使用alter table语句添加索引
    3.alter table支持修改row_format
    4.alter table rename 表名
    5.alter table支持禁用和启用非唯一索引
    6.alter table删除索引
    7.alter table删除外键约束和主键约束
    8.alter table:重建表
    9.alter table修改索引名
    10.alter tabletimestamp列增加/删除on update current_timestamp属性
    11.使用gs_dump工具导出纯文本格式
    12.gsql导入
    13.查询导入后表信息
    14.查询导入后表可选项信息
    15.查询rename后的表信息
    16.查询禁用及删除索引后的表
    17.查询重命名索引后的表
    18.查询修改列属性后的表
    19.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.表1导入成功，含索引
    14.表导入成功，row_format选项不显示
    15.rename后的表导入成功
    16.表导入成功，表6索引禁用，表7索引可用，表8~表11的索引删除成功
    17.表导入成功，索引导入成功
    18.表导入成功
    19.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0049(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_dump_0049"
        self.tb_name = "tb_dump_0049"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0049')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '----step2: 兼容b库下建表后使用alter table语句添加索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(id int)compression=pglz;
        alter table {self.tb_name} add index index_id using btree(id)\
        comment 'id列索引' visible;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step3: alter table支持修改row_format expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table {self.tb_name}_01,{self.tb_name}_02," \
                  f"{self.tb_name}_03,{self.tb_name}_04;" \
                  f"create table {self.tb_name}_01(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_01 row_format = dynamic;" \
                  f"create table {self.tb_name}_02(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_02 row_format = compressed;" \
                  f"create table {self.tb_name}_03(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_03 row_format = compact;" \
                  f"create table {self.tb_name}_04(c int) " \
                  f"row_format = dynamic;" \
                  f"alter table {self.tb_name}_04 row_format = default;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(4,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(4,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step4: alter table rename 表名 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_05;" \
                  f"create table {self.tb_name}_05(c int);" \
                  f"alter table {self.tb_name}_05 rename as " \
                  f"new_{self.tb_name}_05;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step5: alter table支持禁用和启用非唯一索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_06," \
                  f"{self.tb_name}_07;" \
                  f"create table {self.tb_name}_06(c int, " \
                  f"index using btree(c));" \
                  f"alter table {self.tb_name}_06 disable keys;" \
                  f"create table {self.tb_name}_07(c int, " \
                  f"index using hash(c));" \
                  f"alter table {self.tb_name}_07 enable keys;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step6: alter table删除索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_08,{self.tb_name}_09;" \
                  f"create table {self.tb_name}_08(c int, index " \
                  f"alter_index_001 using btree(c));" \
                  f"alter table {self.tb_name}_08 drop index " \
                  f"alter_index_001;" \
                  f"create table {self.tb_name}_09(c int, index " \
                  f"alter_index_002 using hash(c));" \
                  f"alter table {self.tb_name}_09 drop key alter_index_002;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step7: alter table删除外键约束和主键约束 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_10," \
                  f"{self.tb_name}_11;" \
                  f"create table {self.tb_name}_10(c int primary key);" \
                  f"create table {self.tb_name}_11(c int not null, " \
                  f"constraint fk_symbo foreign key(c) references " \
                  f"{self.tb_name}_10(c));" \
                  f"alter table {self.tb_name}_11 drop foreign key fk_symbo;" \
                  f"alter table {self.tb_name}_10 drop primary key;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(3,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step8: alter table:重建表 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_12," \
                  f"{self.tb_name}_13;" \
                  f"create table {self.tb_name}_12(c int);" \
                  f"alter table {self.tb_name}_12 force;" \
                  f"create table {self.tb_name}_13(c int, index " \
                  f"using hash(c));" \
                  f"alter table {self.tb_name}_13 force;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertEqual(2, sql_result.count('VACUUM'), '执行失败:' + text)

        text = '----step9: alter table修改索引名 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_14," \
                  f"{self.tb_name}_15,{self.tb_name}_16,{self.tb_name}_17;" \
                  f"create table {self.tb_name}_14(c int, index idx1 " \
                  f"using btree(c));" \
                  f"create table {self.tb_name}_15(c int, index idx2 " \
                  f"using hash(c));" \
                  f"create table {self.tb_name}_16(c tsvector, index idx3 " \
                  f"using gin(c));" \
                  f"create table {self.tb_name}_17(c tsquery, index idx4 " \
                  f"using gist(c));" \
                  f"alter table {self.tb_name}_14 rename key idx1 to " \
                  f"index_{self.tb_name}_14;" \
                  f"alter table {self.tb_name}_15 rename index idx2 to " \
                  f"key_{self.tb_name}_15;" \
                  f"alter table {self.tb_name}_16 rename index idx3 to " \
                  f"index_{self.tb_name}_16;" \
                  f"alter table {self.tb_name}_17 rename key idx4 to " \
                  f"key_{self.tb_name}_17;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(4,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(4,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step10: alter tabletimestamp列增加/删除on update ' \
               'current_timestamp属性 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_18," \
                  f"{self.tb_name}_19;" \
                  f"create table {self.tb_name}_18(c timestamp);" \
                  f"alter table {self.tb_name}_18 modify c timestamp " \
                  f"on update current_timestamp;" \
                  f"create table {self.tb_name}_19(c timestamp on update " \
                  f"current_timestamp);" \
                  f"alter table {self.tb_name}_19 modify c timestamp;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step11: 使用gs_dump工具导出纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port}  " \
                      f"{self.db_name} " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully", dump_msg,
                      '执行失败:' + text)

        text = '----step12: gsql导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gsql " \
                   f"-p {self.dbuser_node.db_port} " \
                   f"{self.db_name}_01 " \
                   f"-f {self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertEqual(20, gsql_msg.count('CREATE TABLE'))

        text = '----step13: 查询导入后表信息 expect:表1导入成功，含索引----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('CREATE INDEX index_id ON tb_dump_0049 USING btree (id)',
                      sql_result)

        text = '----step14: 查询导入后表可选项信息 expect:表导入成功，' \
               'row_format选项不显示--'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_01;" \
                  f"show create table {self.tb_name}_02;" \
                  f"show create table {self.tb_name}_03;" \
                  f"show create table {self.tb_name}_04;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertNotIn('row_format', sql_result, '执行失败:' + text)

        text = '----step15: 查询rename后的表信息 expect:rename后的表导入成功----'
        self.log.info(text)
        sql_cmd = f"show create table new_{self.tb_name}_05;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('new_tb_dump_0049_05', sql_result, '执行失败:' + text)

        text = '----step16: 查询禁用及删除索引后的表 expect:表导入成功，表6索引禁用，' \
               '表7索引可用，表8~表11的索引删除成功----'
        self.log.info(text)
        sql_cmd = f"show index from {self.tb_name}_06;" \
                  f"show index from {self.tb_name}_07;" \
                  f"show index from {self.tb_name}_08;" \
                  f"show index from {self.tb_name}_09;" \
                  f"show index from {self.tb_name}_10;" \
                  f"show index from {self.tb_name}_11;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('disabled', sql_result, '执行失败:' + text)
        self.assertIn('tb_dump_0049_07_c_idx', sql_result, '执行失败:' + text)
        self.assertEqual(4, sql_result.count('(0 rows)'), '执行失败:' + text)

        text = '----step17: 查询重命名索引后的表 expect:表导入成功，索引导入成功----'
        self.log.info(text)
        sql_cmd = f"show index from {self.tb_name}_12;" \
                  f"show index from {self.tb_name}_13;" \
                  f"show index from {self.tb_name}_14;" \
                  f"show index from {self.tb_name}_15;" \
                  f"show index from {self.tb_name}_16;" \
                  f"show index from {self.tb_name}_17;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        msg_lis = ['(0 rows)', 'tb_dump_0049_13_c_idx',
                   'index_tb_dump_0049_14', 'key_tb_dump_0049_15',
                   'index_tb_dump_0049_16', 'key_tb_dump_0049_17']
        for msg in msg_lis:
            self.assertIn(msg, sql_result, '查询失败')

        text = '----step18: 查询修改列属性后的表 expect:表导入成功----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_18;" \
                  f"show create table {self.tb_name}_19;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn(
            'c timestamp(0) with time zone ON UPDATE CURRENT_TIMESTAMP(0)',
            sql_result)
        self.assertIn('c timestamp(0) with time zone', sql_result)

    def tearDown(self):
        text = '---step19: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
